package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.ICoreDeptDao;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.DeptDto;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TreeDto;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class CoreDeptDaoImpl extends BaseDaoImpl implements ICoreDeptDao {

	private static Logger logger = LogUtil.getLog(CoreDeptDaoImpl.class);
	
	@Override
	public Page<CoreDept> queryCoreDeptPage(Page<CoreDept> page,String channnelCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select t1.dept_code as deptCode, t1.dept_cname as deptCNname from (select t.dept_code, t.dept_cname from t_core_dept t  ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CoreDept coreDept= page.getType();
			
			GatewayUser gatewayuser =  (GatewayUser)page.getObj();
			/*if(null != gatewayuser){
				if(StrUtil.isNotEmpty(gatewayuser.getDepartmentStr())){
					sqlbuf.append(" connect by prior dept_code = snr_dept start with dept_code = ? ");
					list.add(gatewayuser.getDepartmentStr());
					islike.add(false);
				}
			}*/
			sqlbuf.append("   ) t1 where  1=1 ");//length(t1.dept_code) = 8
			if(StrUtil.isNotEmpty(coreDept.getDeptCode())){
				sqlbuf.append(" and t1.dept_code like ? ");
				list.add(coreDept.getDeptCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(coreDept.getDeptCname())){
				sqlbuf.append(" and t1.dept_cname like ? "); // modified by gzz@20150421 for 出单机构按照 机构名称查询 报错问题
				list.add(coreDept.getDeptCname());
				islike.add(true);
			}
			logger.info("----"+channnelCode);
			
					
			sqlbuf.append(" order by t1.dept_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<CoreDept> queryBranchDeptOfOneUser(Page<CoreDept> page,GatewayUser user) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		List lists = null;
		try{
			if(StrUtil.isNotEmpty(user.getDepartmentStr())){
				StringBuffer sqlbuf=new StringBuffer();
				List<String> list=new ArrayList<String>(2);
				List<Boolean> islike=new ArrayList<Boolean>(2);
				conn = this.getConnection();
				sqlbuf.append("SELECT distinct T.DEPT_CODE, T.DEPT_CNAME, T.SNR_DEPT  FROM T_CORE_DEPT T WHERE 1=1 ");
				if(StrUtil.isNotEmpty(page.getType().getDeptCode())){
					sqlbuf.append(" AND T.DEPT_CODE = ? ");
					list.add(page.getType().getDeptCode());
					islike.add(false);
				}
				if(StrUtil.isNotEmpty(page.getType().getDeptCname())){
					sqlbuf.append(" AND T.DEPT_CNAME like ? ");
					list.add(page.getType().getDeptCname());
					islike.add(true);
				}
				if(user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE)){
					sqlbuf.append(" START WITH T.DEPT_CODE ='00' CONNECT BY NOCYCLE T.SNR_DEPT = PRIOR T.DEPT_CODE ");
				}else{
					sqlbuf.append("START WITH T.DEPT_CODE IN ("+user.getDepartmentStr()+") CONNECT BY NOCYCLE T.SNR_DEPT = PRIOR T.DEPT_CODE ");
				}
				sqlbuf.append(" ORDER BY T.DEPT_CODE ");
				
				String sql;
				sql=sqlbuf.toString();
				logger.debug("执行SQL：" + sql);
				
				pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
				for (int i = 0; i < list.size(); i++) {
					if(islike.get(i)){
						pstmt.setObject(i+1, "%"+list.get(i)+"%");
					}else{
						pstmt.setObject(i+1, list.get(i));
					}
					
				}
				rs = pstmt.executeQuery();
				rs.next();
				Long total = rs.getLong(1);
				page.setTotal(total);
				//查出分页
				SqlUtil sqlUtil=new	SqlUtil();
				String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
				logger.debug("执行SQL："+pagesql);
				pstmt1 = conn.prepareStatement(pagesql);
				for (int i = 0; i < list.size(); i++) {
					if(islike.get(i)){
						pstmt1.setObject(i+1, "%"+list.get(i)+"%");
					}else{
						pstmt1.setObject(i+1, list.get(i));
					}
				}
				rs = pstmt1.executeQuery();
				lists = DBUtil.transRs2List(rs);
			}
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	
	public static  String transDeptList2deptCodeStr(List<DeptDto> list){
		StringBuilder sb = new StringBuilder("");
		
		if(list != null && list.size() > 0){
			for (DeptDto deptDto : list) {
				sb.append(deptDto.getDeptCode() + "','" );
			}
			String deptCodeStr = sb.substring(0, sb.length()-3);
			return deptCodeStr;
		}
		
		return "";
	}

	@Override
	public List<TreeDto> queryDeptOfOneUser(GatewayUser user) throws Exception {

		String deptStr = user.getDepartmentStr();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TreeDto> deptTree = new ArrayList<TreeDto>();
		
		StringBuffer sqlbuf=new StringBuffer();
		
		sqlbuf.append("select t.dept_code,t.dept_cname,t.snr_dept from  t_core_dept t where 1=1 ");
		sqlbuf.append(" connect by prior t.dept_code = t.snr_dept start with t.dept_code in ("+deptStr+")");
		sqlbuf.append(" group by t.dept_code,t.dept_cname,t.snr_dept");
		try{
			conn = this.getConnection();
			String sql = sqlbuf.toString();
			logger.info("执行SQL："+sql);
			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				TreeDto tree = new TreeDto();
				tree.setId(rs.getString(1));
				tree.setName(rs.getString(2));
				tree.setpId(rs.getString(3));
				
				deptTree.add(tree);
			}
		}finally{
			close(rs);
			close(pstmt);
			close(conn);
		}
		
		return deptTree;
	}

	@Override
	public Page<CoreDept> queryCoreDeptPageByParentCode(Page<CoreDept> page,
			String parentDeptCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select t1.dept_code as deptCode, t1.dept_cname as deptCNname from (select t.dept_code, "
					+ "t.dept_cname from t_core_dept t connect by nocycle prior t.dept_code = t.snr_dept start with dept_code = ? ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CoreDept coreDept= page.getType();
			
			if(StrUtil.isNotEmpty(parentDeptCode)){
				list.add(parentDeptCode);
				islike.add(false);
			}
			sqlbuf.append("   )  t1  where length(t1.dept_code) = 8 ");
			if(StrUtil.isNotEmpty(coreDept.getDeptCode())){
				sqlbuf.append(" and t1.dept_code like ? ");
				list.add(coreDept.getDeptCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(coreDept.getDeptCname())){
				sqlbuf.append(" and t1.dept_cname like ? "); // modified by gzz@20150421 for 出单机构按照 机构名称查询 报错问题
				list.add(coreDept.getDeptCname());
				islike.add(true);
			}
			logger.info("----"+parentDeptCode);
			
					
			sqlbuf.append(" order by t1.dept_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public List<CoreDept> queryDeptList(String deptStr) throws Exception {
		return this.queryList("queryCoreDeptByUserPrivilegeDept", deptStr);
	}

	@Override
	public boolean checkIsHasPrivilegeDept(CoreDept coreDept) throws Exception {
		int a = (Integer) this.query("queryUserIsHasPrivilegeDept", coreDept);
		if (a > 0) {
			return true;
		}
		return false;
	}

	@Override
	public Page<CoreDept> queryCoreDeptPage1(Page<CoreDept> page,
			String partnerNo ,GatewayUser gatewayuser) throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select distinct t1.dept_code as deptCode, t1.dept_cname as deptCNname from (select t.dept_code, t.dept_cname from t_core_dept t connect by prior dept_code = snr_dept ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CoreDept coreDept= page.getType();
			/*if(StrUtil.isNotEmpty(partnerNo)){
				sqlbuf.append(" start with dept_code = (select l.dept_code from T_GATEWAY_CHANNEL_INFO l  where 1=1  and l.CHANNEL_CODE = ? )   ");
				list.add(partnerNo);
				islike.add(false);
			}*/
			//登陆用户所属出单机构 
			String userDeptCode = gatewayuser.getDepartmentStr().substring(1, 3);
			if(StrUtil.isNotEmpty(userDeptCode)){
				sqlbuf.append(" start with dept_code = ?   ");
				list.add(userDeptCode);
				islike.add(false);
			}
			sqlbuf.append("  ) t1  where 1=1 ");//where length(t1.dept_code) = 8
			if(StrUtil.isNotEmpty(coreDept.getDeptCode())){
				sqlbuf.append(" and t1.dept_code like ?");
				list.add(coreDept.getDeptCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(coreDept.getDeptCname())){
				sqlbuf.append(" and t1.dept_cname like ? "); // modified by gzz@20150421 for 出单机构按照 机构名称查询 报错问题
				list.add(coreDept.getDeptCname());
				islike.add(true);
			}
			logger.info("----"+partnerNo);
			
					
			sqlbuf.append(" order by t1.dept_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

	@Override
	public Page<CoreDept> queryCoreDeptPage2(Page<CoreDept> page,
			String deptCode) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		Map<String, Object> map=page.getMap();
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select distinct t.dept_code, t.dept_code ||'-'|| t.dept_abr as dept_cname  from t_core_dept t ");
			sqlbuf.append(" where  length(dept_code)<5 ");
			String dptCode="";
			if(map.get("deptCode")!=null){
				 dptCode=(String) map.get("deptCode");
				 list.add(dptCode);
			}
			
			if(StrUtil.isNotEmpty(dptCode)){
				sqlbuf.append(" and  t.dept_code like ? ");
			}
			String dptName="";
			if(map.get("deptName")!=null){
				dptName=(String) map.get("deptName");
				list.add(dptName);
			}
			 
			if(StrUtil.isNotEmpty(dptName)){
				sqlbuf.append(" and  t.dept_cname like ? ");
			}
			sqlbuf.append("	start with t.dept_code in ("+deptCode+" )");
			sqlbuf.append("	connect by nocycle t.snr_dept = prior t.dept_code ");
			sqlbuf.append(" order by t.dept_code");
			String sql;
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i+1, list.get(i));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				pstmt1.setObject(i+1, list.get(i));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public Page<CoreDept> queryPageDept(Page<CoreDept> page, String deptCode)
			throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		Map<String, Object> map=page.getMap();
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select distinct t.dept_code, t.dept_code ||'-'|| t.dept_abr as dept_cname  from t_core_dept t ");
			sqlbuf.append(" where  length(dept_code) in (2,4,6,8)");
			String dptCode="";
			if(map.get("deptCode")!=null){
				 dptCode=(String) map.get("deptCode");
			}
			
			if(StrUtil.isNotEmpty(dptCode)){
				sqlbuf.append(" and  t.dept_code like ? ");
				list.add(dptCode);
			}
			String dptName="";
			if(map.get("deptName")!=null){
				dptName=(String) map.get("deptName");
				list.add(dptName);
			}
			 
			if(StrUtil.isNotEmpty(dptName)){
				sqlbuf.append(" and  t.dept_cname like ? ");
			}
			sqlbuf.append("	start with t.dept_code in ("+deptCode+" )");
			sqlbuf.append("	connect by nocycle t.snr_dept = prior t.dept_code ");
			sqlbuf.append(" order by  length(dept_code), t.dept_code");
			String sql;
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 1; i <=list.size(); i++) {
				pstmt.setObject(i, list.get(i-1));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 1; i <=list.size(); i++) {
				pstmt1.setObject(i, list.get(i-1));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

	@Override
	public List<CoreDept> queryDeptNameList(Map<String, Object> map)
			throws Exception {
		return this.queryList("queryDeptNames", map);
	}

	@Override
	public Page<CoreDept> queryCoreDeptPageJSCD(Page<CoreDept> page,
			String channnelCode) throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select t1.dept_code as deptCode, t1.dept_cname as deptCNname from (select t.dept_code, t.dept_cname from t_core_dept t  ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CoreDept coreDept= page.getType();
			
			GatewayUser gatewayuser =  (GatewayUser)page.getObj();
			if(gatewayuser!=null&&StrUtil.isNotEmpty(gatewayuser.getDepartmentStr().substring(1,3))){
				sqlbuf.append(" connect by prior dept_code = snr_dept start with dept_code = ? ");
				list.add(gatewayuser.getDepartmentStr().substring(1,3));
				islike.add(false);
			}
			sqlbuf.append("   ) t1 where  1=1 ");//length(t1.dept_code) = 8
			if(StrUtil.isNotEmpty(coreDept.getDeptCode())){
				sqlbuf.append(" and t1.dept_code like ? ");
				list.add(coreDept.getDeptCode());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(coreDept.getDeptCname())){
				sqlbuf.append(" and t1.dept_cname like ? "); // modified by gzz@20150421 for 出单机构按照 机构名称查询 报错问题
				list.add(coreDept.getDeptCname());
				islike.add(true);
			}
			logger.info("----"+channnelCode);
			
					
			sqlbuf.append(" order by t1.dept_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<CoreDept> queryForTwo() throws Exception{
		return (List<CoreDept>)this.queryList("queryDeptForTwo","");
	}
	@Override
	public Page<CoreDept> queryCoreDeptForCar(Page<CoreDept> page,String dept) throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		Map<String, Object> map=page.getMap();
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select distinct t.dept_code, dept_cname  from t_core_dept t ");
			sqlbuf.append(" where  length(dept_code) in (2,4,6,8)");
			String dptCode="";
			if(map.get("deptCode")!=null){
				 dptCode=(String) map.get("deptCode");
			}
			
			if(StrUtil.isNotEmpty(dptCode)){
				sqlbuf.append(" and  t.dept_code like ? ");
				list.add(dptCode);
			}
			String dptName="";
			if(map.get("deptName")!=null){
				dptName=(String) map.get("deptName");
				list.add(dptName);
			}
			 
			if(StrUtil.isNotEmpty(dptName)){
				sqlbuf.append(" and  t.dept_cname like ? ");
			}
			sqlbuf.append("	start with t.dept_code in ("+dept+" )");
			sqlbuf.append("	connect by nocycle t.snr_dept = prior t.dept_code ");
			sqlbuf.append(" order by  length(dept_code), t.dept_code");
			String sql;
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 1; i <=list.size(); i++) {
				pstmt.setObject(i, list.get(i-1));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 1; i <=list.size(); i++) {
				pstmt1.setObject(i, list.get(i-1));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	
	}

	@Override
	public List<CoreDept> queryProvenceDept() throws Exception {
		Map<String,Object> para = null;
		return this.queryList("queryProvenceDept", para);
	}

	@Override
	public List<CoreDept> queryProvenceAndNextLevelDept() throws Exception {
		Map<String,Object> para = null;
		return this.queryList("queryProvenceAndNextLevelDept", para);
	}

	@Override
	public String queryDeptNameByDptCde(String dptCde) throws Exception {
		return (String) this.query("queryDeptNameByDptCde",dptCde);
	}
}
